package com.news.dao;

import com.news.model.News;
import com.news.model.Sort;
import com.news.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;

public class SortDao {
    private JdbcTemplate jdbcTemplate = null;

    public SortDao() {
        jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    }

    /**
     * 获取所有分类
     */
    public List<Sort> getAllSorts() {
        String sql = "select * from tb_sort";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Sort>(Sort.class));
    }

    /**
     * 分页获取分类
     */
    public List<Sort> getSortsByPage(int offset, int pageSize) {
        String sql = "select * from tb_sort limit ?, ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Sort>(Sort.class), offset, pageSize);
    }

    /**
     * 获取分类总数
     */
    public int getTotalCount() {
        String sql = "select count(*) from tb_sort";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 根据关键词搜索分类
     */
    public List<Sort> searchSorts(String keyword, int offset, int pageSize) {
        String sql = "select * from tb_sort where name like ? limit ?, ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Sort>(Sort.class), 
                "%" + keyword + "%", offset, pageSize);
    }

    /**
     * 获取搜索结果总数
     */
    public int getSearchCount(String keyword) {
        String sql = "select count(*) from tb_sort where name like ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, "%" + keyword + "%");
    }

    /**
     * 根据ID获取分类
     */
    public Sort getSortById(int sortId) {
        String sql = "select * from tb_sort where sort_id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Sort>(Sort.class), sortId);
    }

    /**
     * 添加分类
     */
    public boolean addSort(Sort sort) {
        String sql = "insert into tb_sort(name) values(?)";
        return jdbcTemplate.update(sql, sort.getName()) > 0;
    }

    /**
     * 更新分类
     */
    public boolean updateSort(Sort sort) {
        String sql = "update tb_sort set name = ? where sort_id = ?";
        return jdbcTemplate.update(sql, sort.getName(), sort.getSort_id()) > 0;
    }

    /**
     * 删除分类
     */
    public boolean deleteSort(int sortId) {
        String sql = "delete from tb_sort where sort_id = ?";
        return jdbcTemplate.update(sql, sortId) > 0;
    }
}
